﻿Imports System.Data.OleDb

Module XL_ThuCoDinh
    Structure ThuCoDinh
        Private _iD As Integer
        Property ID() As Integer
            Get
                Return _iD
            End Get
            Set(ByVal value As Integer)
                _iD = value
            End Set
        End Property
        Private _ngay As Integer
        Property Ngay() As Integer
            Get
                Return _ngay
            End Get
            Set(ByVal value As Integer)
                _ngay = value
            End Set
        End Property
        Private _soTien As Integer
        Property SoTien() As Integer
            Get
                Return _soTien
            End Get
            Set(ByVal value As Integer)
                _soTien = value
            End Set
        End Property
        Private _loaiThu As Integer
        Property LoaiThu() As Integer
            Get
                Return _loaiThu
            End Get
            Set(ByVal value As Integer)
                _loaiThu = value
            End Set
        End Property
        Private _taiKhoan As Integer
        Property TaiKhoan() As Integer
            Get
                Return _taiKhoan
            End Get
            Set(ByVal value As Integer)
                _taiKhoan = value
            End Set
        End Property
        Private _nguoiThu As Integer
        Property NguoiThu() As Integer
            Get
                Return _nguoiThu
            End Get
            Set(ByVal value As Integer)
                _nguoiThu = value
            End Set
        End Property
        Private _chiTiet As String
        Property ChiTiet() As String
            Get
                Return _chiTiet
            End Get
            Set(ByVal value As String)
                _chiTiet = value
            End Set
        End Property

        Private _lanCuoi As Date
        Property LanCuoi() As Date
            Get
                Return _lanCuoi
            End Get
            Set(ByVal value As Date)
                _lanCuoi = value
            End Set
        End Property
        
    End Structure

    Public Function LayDS(ByVal ngthu As Integer) As List(Of ThuCoDinh)
        Dim ds As New List(Of ThuCoDinh)
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("Select * from ThuCoDinh where NguoiThu=@NguoiThu", con)
            cmd.Parameters.AddWithValue("@NguoiThu", ngthu)
            Dim rd As OleDbDataReader = cmd.ExecuteReader()
            While rd.Read()
                Dim p As ThuCoDinh = New ThuCoDinh()
                p.ID = rd.GetInt32(0)
                p.Ngay = rd.GetInt32(1)
                p.SoTien = rd.GetInt32(2)
                p.LoaiThu = rd.GetInt32(3)
                p.TaiKhoan = rd.GetInt32(4)
                p.NguoiThu = rd.GetInt32(5)
                p.ChiTiet = rd.GetString(6)
                p.LanCuoi = rd.GetDateTime(7)
                ds.Add(p)
            End While
            con.Close()
            Return (ds)
        Catch ex As Exception
            Return ds
        End Try
    End Function

    Public Function Lay(ByVal id As Integer) As ThuCoDinh
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("Select * from ThuCoDinh where ID=@ID", con)
            cmd.Parameters.AddWithValue("@ID", id)
            Dim rd As OleDbDataReader = cmd.ExecuteReader()
            Dim p As ThuCoDinh = New ThuCoDinh()
            If rd.Read() Then
                p.ID = rd.GetInt32(0)
                p.Ngay = rd.GetInt32(1)
                p.SoTien = rd.GetInt32(2)
                p.LoaiThu = rd.GetInt32(3)
                p.TaiKhoan = rd.GetInt32(4)
                p.NguoiThu = rd.GetInt32(5)
                p.LanCuoi = rd.GetDateTime(7)
            End If
            con.Close()
            Return p
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function Them(ByVal p As ThuCoDinh) As Boolean
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("insert into ThuCoDinh(Ngay, SoTien, LoaiThu,TaiKhoan,NguoiThu,ChiTiet,LanCuoi) values(@Ngay, @SoTien, @LoaiThu,@TaiKhoan,@NguoiThu,@ChiTiet,@LanCuoi)", con)
            cmd.Parameters.AddWithValue("@Ngay", p.Ngay)
            cmd.Parameters.AddWithValue("@SoTien", p.SoTien)
            cmd.Parameters.AddWithValue("@LoaiThu", p.LoaiThu)
            cmd.Parameters.AddWithValue("@TaiKhoan", p.TaiKhoan)
            cmd.Parameters.AddWithValue("@NguoiThu", p.NguoiThu)
            cmd.Parameters.AddWithValue("@ChiTiet", p.ChiTiet)
            cmd.Parameters.AddWithValue("@LanCuoi", p.LanCuoi)
            Dim kq = cmd.ExecuteNonQuery()
            cmd.CommandText = "Select @@Identity"
            p.ID = cmd.ExecuteScalar()
            con.Close()
            Return kq
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function Xoa(ByVal p As ThuCoDinh) As Integer
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("delete from ThuCoDinh where ID = @ID", con)
            cmd.Parameters.AddWithValue("@ID", p.ID)
            Dim kq = cmd.ExecuteNonQuery()
            con.Close()
            Return kq
        Catch ex As Exception
            Return 0
        End Try
    End Function

    Public Function CapNhat(ByVal p As ThuCoDinh) As Integer
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("update ThuCoDinh set Ngay=@Ngay, SoTien=@SoTien, LoaiThu=@LoaiThu,TaiKhoan=@TaiKhoan,NguoiThu=@NguoiThu,ChiTiet=@ChiTiet,LanCuoi=@LanCuoi where ID = @ID", con)
            cmd.Parameters.AddWithValue("@Ngay", p.Ngay)
            cmd.Parameters.AddWithValue("@SoTien", p.SoTien)
            cmd.Parameters.AddWithValue("@LoaiThu", p.LoaiThu)
            cmd.Parameters.AddWithValue("@TaiKhoan", p.TaiKhoan)
            cmd.Parameters.AddWithValue("@NguoiThu", p.NguoiThu)
            cmd.Parameters.AddWithValue("@ChiTiet", p.ChiTiet)
            cmd.Parameters.AddWithValue("@LanCuoi", p.LanCuoi)
            cmd.Parameters.AddWithValue("@ID", p.ID)
            Dim kq = cmd.ExecuteNonQuery()
            con.Close()
            Return kq
        Catch ex As Exception
            Return 0
        End Try
    End Function

    Function TuDongPhatSinh(ByVal user As Integer) As Integer
        Dim ds = XL_ThuCoDinh.LayDS(user)

        Dim kq As Integer = 0
        For Each c In ds
            Dim ngayps = New Date(Now.Year, Now.Month, c.Ngay)
            If c.LanCuoi <> ngayps Then
                Dim count = DateDiff(DateInterval.Month, c.LanCuoi, Now.Date)
                kq += count
                While count > 0
                    Dim thang = Now.Month - count + 1
                    Dim nam = Now.Year
                    If thang < 0 Then
                        thang += 12
                        nam -= 1
                    End If
                    Dim p As New PhieuThu
                    p.Ngay = New Date(nam, thang, c.Ngay)
                    p.LoaiThu = c.LoaiThu
                    p.NguoiThu = c.NguoiThu
                    p.NguoiLap = c.NguoiThu
                    p.SoTien = c.SoTien
                    p.TaiKhoan = c.TaiKhoan
                    p.ChiTiet = c.ChiTiet
                    XL_PhieuThu.Them(p)
                    count -= 1
                End While
                c.LanCuoi = ngayps
                XL_ThuCoDinh.CapNhat(c)
            End If
        Next
        Return kq
    End Function

    'Public Function TuDongPhatSinh(ByVal nd As Integer) As Integer
    '    Try
    '        Dim kqt As Integer = 0
    '        Dim ng As Integer = 0
    '        Dim th As Integer = 0
    '        Dim nam As Integer = 0
    '        Dim con = DataProvider.MoKetNoi()
    '        con.Open()
    '        Dim cmd As OleDbCommand = New OleDbCommand("select * from ThuCoDinh where NguoiThu=@nt and LanCuoi<=@t", con)
    '        cmd.Parameters.AddWithValue("@nt", nd)
    '        cmd.Parameters.AddWithValue("@t", Now.Month)
    '        Dim rd As OleDbDataReader = cmd.ExecuteReader()
    '        While rd.Read()
    '            th = rd.GetInt32(7)
    '            Dim pt As New PhieuThu
    '            pt.ID = rd.GetInt32(0)
    '            pt.Ngay = rd.GetDateTime(1)
    '            pt.SoTien = rd.GetInt32(2)
    '            pt.LoaiThu = rd.GetInt32(3)
    '            pt.TaiKhoan = rd.GetInt32(4)
    '            pt.NguoiThu = rd.GetInt32(5)
    '            pt.ChiTiet = rd.GetString(6)
    '            pt.NguoiLap = rd.GetInt32(8)

    '            If th < Now.Month And rd.GetDateTime(1).Day - 1 >= Now.Day Then
    '                While (th < Now.Month)
    '                    Dim kq As Integer = XL_PhieuThu.Them(pt)
    '                    If kq = 1 Then
    '                        th = th + 1
    '                    End If
    '                End While

    '            ElseIf th < Now.Month And rd.GetDateTime(1).Day - 1 < Now.Day Then
    '                While (th <= Now.Month)
    '                    Dim kq As Integer = XL_PhieuThu.Them(pt)
    '                    If kq = 1 Then
    '                        th = th + 1
    '                    End If
    '                End While
    '            ElseIf th = Now.Month And rd.GetDateTime(1).Day - 1 < Now.Day Then
    '                Dim kq As Integer = XL_PhieuThu.Them(pt)
    '                If kq = 1 Then
    '                    th = th + 1
    '                End If
    '            End If

    '            Dim c As OleDbCommand = New OleDbCommand("update ThuCoDinh set LanCuoi=@gt where ID = @ID", con)
    '            c.Parameters.AddWithValue("@gt", th)
    '            c.Parameters.AddWithValue("@ID", rd.GetInt32(0))
    '            Dim x = c.ExecuteNonQuery()

    '            kqt = kqt + 1
    '        End While
    '        con.Close()
    '        Return kqt
    '    Catch ex As Exception
    '        Return 0
    '    End Try
    'End Function
End Module
